﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Configuration;
using System.IO;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;


namespace DatabaseSynchronizer {
    public partial class MainForm : BaseForm {

        private readonly string DatabaseInfoText = "I am working with Database – {0} on {1}.";

        private readonly string BootstrapConfirmation = "The Database Synchronizer needs to create a schema changes table"
                                                        + " to the database to track the changes."
                                                        + Environment.NewLine + "Create it now?";

        private readonly string ErrMsgNoSettings = "To set up the Database Synchronizer, click on the setting button.";
        private readonly string ErrMsgNoBootstrap = "The Database Synchronizer needs to create a schema changes table to operate.";
        private readonly string ErrMsgDuplicatePatches = "Multiple files with the same Patch number found in the Scripts folder.";

        private Settings _settings;
        private IDBService _dbService;
        private List<FileInfo> _localFilesForEntryOnly;
        private List<FileInfo> _newScriptFiles;


        public MainForm(string[] args) {
            InitializeComponent();
        }


        #region Event Handlers

        private void MainForm_Load(object sender, EventArgs e) {

            ClearErrorAndStatus();

            if (!AreSettingsValid()) {
                NoSettingsAvailableView();
            }
            else {
                DataSourceCheckBox.Enabled = true;
                DataSourceCheckBox.Text = string.Format(DatabaseInfoText, _settings.InitialCatalog(), _settings.DataSource());
            }

            DataSourceCheckBox.Checked = false;
            ViewTabs.Enabled = false;
            ReloadScriptsButton.Enabled = false;
            ViewTabs.SelectedIndex = 0;

        }


        private void dataSourceCheckBox_CheckedChanged(object sender, EventArgs e) {

            if (DataSourceCheckBox.Checked) {

                if (!IsBootstrappingDone()) {

                    StartupMsgLabel.Text = ErrMsgNoBootstrap;
                    DataSourceCheckBox.Checked = false;
                }
                else if (DoDuplicatePatchesExist()) {

                    StartupMsgLabel.Text = ErrMsgDuplicatePatches;
                    DataSourceCheckBox.Checked = false;
                }
                else {
                    LoadScriptFiles(true);
                }
            }

            ViewTabs.Enabled = DataSourceCheckBox.Checked;
            ReloadScriptsButton.Enabled = DataSourceCheckBox.Checked;
        }


        private void updateDBbutton_Click(object sender, EventArgs e) {

            int successCount = 0;
            string errorMsg;
            ClearErrorAndStatus();

            foreach (var file in _newScriptFiles) {

                StatusLabel.Text = "Executing script - " + file.Name;

                if (_dbService.ExecuteScriptFile(file, out errorMsg)) {

                    successCount++;
                }
                else {

                    StatusLabel.Text = "Error occured while executing - " + file.Name
                        + Environment.NewLine + errorMsg;
                    StatusLabel.ForeColor = Color.Red;
                    break;
                }
            }

            if (successCount == _newScriptFiles.Count) {
                ClearErrorAndStatus();
                LoadScriptFiles(true);
                SummaryLabel.Text = "All scripts successfully executed.";
            }
            else {
                SummaryLabel.Text = successCount + " of " + _newScriptFiles.Count + " scripts executed.";
            }
        }


        private void updateLoalScriptButton_Click(object sender, EventArgs e) {

            openScriptFileDialog.InitialDirectory = _settings.LocalScriptsFolder;
            openScriptFileDialog.ShowDialog();
        }


        private void openScriptFileDialog_FileOk(object sender, CancelEventArgs e) {

            ClearErrorAndStatus();
            string errorMsg = string.Empty;

            _localFilesForEntryOnly = (from fileNameWithPath in openScriptFileDialog.FileNames
                                       select new FileInfo(fileNameWithPath)).ToList();

            _localFilesForEntryOnly.Sort((f1, f2) => f1.PatchId() - f2.PatchId());

            selectedFileLabel.Text = string.Empty;
            string fileEntry = string.Empty;

            List<string> alreadyExisting = new List<string>();

            foreach (var file in _localFilesForEntryOnly) {

                if (_dbService.IsPatchExecuted(file.PatchId(), out errorMsg)) {

                    fileEntry = "Already exists -> " + file.Name;
                    alreadyExisting.Add(file.Name);

                }
                else if (!string.IsNullOrEmpty(errorMsg)) {

                    selectedFileLabel.Text = "Error - " + errorMsg;
                    selectedFileLabel.ForeColor = Color.Red;
                    goButton.Enabled = false;
                    return;
                }
                else {
                    selectedFileLabel.ForeColor = Color.Black;
                    fileEntry = file.Name;
                }

                if (selectedFileLabel.Text != string.Empty) {

                    selectedFileLabel.Text = selectedFileLabel.Text + Environment.NewLine + fileEntry;
                }
                else {

                    selectedFileLabel.Text = fileEntry;
                }
            }

            if (_localFilesForEntryOnly.Count - alreadyExisting.Count == 0) {

                SelectedTotalCountLabel.Text = "No new scripts to update.";
                goButton.Enabled = false;
            }
            else {
                SelectedTotalCountLabel.Text = _localFilesForEntryOnly.Count + " scripts selected, " +
                   (_localFilesForEntryOnly.Count - alreadyExisting.Count) + " eligible for update.";
                goButton.Enabled = true;
            }

            _localFilesForEntryOnly.RemoveAll(f => alreadyExisting.Contains(f.Name));
        }


        private void goButton_Click(object sender, EventArgs e) {

            int count = 0;
            string errorMsg = string.Empty;

            _localFilesForEntryOnly.Sort((f1, f2) => f1.PatchId() - f2.PatchId());

            int total = _localFilesForEntryOnly.Count;

            StatusLabel.ForeColor = Color.Black;
            StatusLabel.Text = string.Empty;

            foreach (var file in _localFilesForEntryOnly) {

                if (_dbService.MarkLocalPatchAsExecuted(file, out errorMsg)) {

                    count++;
                }
                else {

                    StatusLabel.ForeColor = Color.Red;
                    StatusLabel.Text = count + " out of " + _localFilesForEntryOnly.Count + " scripts updated. Check the error and select scripts again."
                        + Environment.NewLine + "Error - " + errorMsg;

                    break;
                }
            }

            if (count > 0) {
                LoadScriptFiles(true);
            }

            SelectedTotalCountLabel.Text = count + " out of " + total + " script(s) updated.";
            SummaryLabel.Text = String.Empty;
            goButton.Enabled = false;

        }


        private void buttonSettings_Click(object sender, EventArgs e) {

            Form settings = new SettingsForm();
            DialogResult dialogResult = settings.ShowDialog(this);
            string errMsg = string.Empty;

            if (dialogResult == DialogResult.OK) {

                _settings = SettingsService.GetSettings(Environment.UserName, Environment.MachineName);
                MainForm_Load(sender, e);
            }
        }


        private void ExecutedSelectedFilesButton_Click(object sender, EventArgs e) {


            if (SelectedRows > 0) {

                int total = SelectedRows;
                int successCount = 0;
                string errorMsg = string.Empty;
                GridStatusLabel.ForeColor = Color.Red;

                foreach (DataGridViewRow gridRow in ScriptsDataGrid.Rows) {

                    var row = gridRow.DataBoundItem as FileRow;
                    if (row.Select) {
                        GridStatusLabel.Text = "Executing script - " + row.Name;

                        if (_dbService.ExecuteScriptFile(new FileInfo(_settings.LocalScriptsFolder + @"\" + row.Name)
                                                            , out errorMsg)) {
                            successCount++;
                        }
                        else {

                            GridStatusLabel.Text = "Error occured while executing - " + row.Name
                                + Environment.NewLine + errorMsg;

                            break;
                        }
                    }
                }

                if (successCount > 0) {

                    LoadScriptFiles(successCount == total);
                }

                if (successCount == total) {

                    GridSummaryLabel.Text = "All " + total + " script(s) successfully executed.";
                    GridStatusLabel.ForeColor = Color.Black;
                    GridStatusLabel.Text = string.Empty;

                }
                else {

                    GridStatusLabel.ForeColor = Color.Red;
                    GridSummaryLabel.Text = successCount + " of " + total + " scripts executed.";
                }
            }
            else {

                MessageBox.Show("No files selected to execute.", "No scripts selected", MessageBoxButtons.OK);
            }

        }


        public void ReloadScriptFiles(object sender, EventArgs e) {

            LoadScriptFiles(true);
            ClearErrorAndStatus();
        }



        private void UnSelectAllLinkLabel_Click(object sender, EventArgs e) {

            SetGridSelect(false);
        }

        private void SelectAllLinkLabel_Click(object sender, EventArgs e) {

            SetGridSelect(true);
        }


        private void AboutButton_Click(object sender, EventArgs e) {
            Form about = new About();
            about.ShowDialog(this);
        }

        #endregion


        #region Methods

        private void NoSettingsAvailableView() {

            StartupMsgLabel.Text = ErrMsgNoSettings;
            DataSourceCheckBox.Text = string.Empty;
            TotalCountLabel.Text = string.Empty;
            DataSourceCheckBox.Enabled = false;
            ViewTabs.Enabled = false;
            ReloadScriptsButton.Enabled = false;

        }

        private bool AreSettingsValid() {

            if (!SettingsService.AValidSettingExistsForUser(Environment.UserName, Environment.MachineName)) {
                return false;
            }
            else {

                _settings = SettingsService.GetSettings(Environment.UserName, Environment.MachineName);
                _dbService = DBServicesFactory.GetDBService(_settings);

                StartupMsgLabel.Text = string.Empty;
                return true;
            }
        }

        private bool IsBootstrappingDone() {

            string errMsg = string.Empty;

            if (!_dbService.IsSchemaChangesTableCreated(out errMsg)) {

                if (MessageBox.Show(BootstrapConfirmation, "Bootstrap Database", MessageBoxButtons.YesNo) == DialogResult.Yes) {

                    if (!_dbService.CreateSchemaChangesTable(out errMsg)) {

                        MessageBox.Show("Error occured - " + errMsg, "Error", MessageBoxButtons.OK);
                        return false;
                    }
                }
                else {
                    return false;
                }
            }
            else if (!string.IsNullOrEmpty(errMsg)) {

                MessageBox.Show("Error occured - " + errMsg, "Error", MessageBoxButtons.OK);
                return false;
            }

            return true;
        }


        private void LoadScriptFiles(bool selectAllNewScripts) {

            string errorMsg;

            var patches = _dbService.GetExecutedPatches(out errorMsg);

            if (!string.IsNullOrEmpty(errorMsg)) {

                StatusLabel.Text = "Error - " + errorMsg;
                return;
            }

            _newScriptFiles = (from file in (new DirectoryInfo(_settings.LocalScriptsFolder)).GetFiles()
                               where file.Name.EndsWith(".sql", StringComparison.CurrentCultureIgnoreCase)
                                         && !patches.Contains(file.PatchId())
                               orderby file.PatchId()
                               select file).ToList();

            TotalCountLabel.Text = _newScriptFiles.Count + " not executed scripts found.";
            GridTotalCountLabel.Text = TotalCountLabel.Text;

            SetGridSelect(selectAllNewScripts);
        }


        private bool DoDuplicatePatchesExist() {

            int totalScripts = (from file in (new DirectoryInfo(_settings.LocalScriptsFolder)).GetFiles()
                                where file.Name.EndsWith(".sql", StringComparison.CurrentCultureIgnoreCase)
                                select file).Count();

            int totalPatches = (from file in (new DirectoryInfo(_settings.LocalScriptsFolder)).GetFiles()
                                where file.Name.EndsWith(".sql", StringComparison.CurrentCultureIgnoreCase)
                                select file.PatchId()).Distinct().Count();

            if (totalPatches != totalScripts) {

            }

            return totalPatches != totalScripts;

        }


        private void SetGridSelect(bool selected) {

            ScriptsDataGrid.DataSource = (from file in _newScriptFiles
                                          orderby file.PatchId()
                                          select new FileRow {
                                              Select = selected,
                                              Patch = file.PatchId(),
                                              Name = file.Name,
                                              Modified = file.LastWriteTime
                                          }).ToList();

            GridStatusLabel.ForeColor = Color.Black;
        }


        private void ClearErrorAndStatus() {

            SummaryLabel.Text = string.Empty;
            StatusLabel.Text = string.Empty;
            StatusLabel.ForeColor = Color.Black;

            GridSummaryLabel.Text = string.Empty;
            GridStatusLabel.Text = string.Empty;
            GridStatusLabel.ForeColor = Color.Black;

            SelectedTotalCountLabel.Text = string.Empty;
            selectedFileLabel.Text = string.Empty;
        }


        #endregion


        #region Properties

        private int SelectedRows {
            get {

                int selected = 0;

                foreach (DataGridViewRow gridRow in ScriptsDataGrid.Rows) {

                    if ((gridRow.DataBoundItem as FileRow).Select) {
                        selected++;
                    }
                }

                return selected;
            }
        }

        #endregion



    }
}
